﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Model;

namespace DB
{
    public class DbTicket : IDbTicket
    {
        private DbConnection con; 
        private SqlCommand dbCmd;

        public DbTicket()
        {
            con = DbConnection.GetInstance();
            dbCmd = null;
        }


        public int InsertTicket(Booking booking, Seat seat, double price, int bookingId)
        {
            int rc = -1;
            string insert = @"INSERT INTO Ticket_xp VALUES(@ticketId, @showId, @price, @seatId, @bookingId)";
            Console.WriteLine(insert);

            IDbShow dbShow = new DbShow();
            int showId = dbShow.GetShowId(booking.Show);

            int nextTicketId = GetNextTicketId();

            try
            {
                dbCmd = con.GetDbCommand(insert);
                dbCmd.Parameters.AddWithValue("@ticketId", nextTicketId);
                dbCmd.Parameters.AddWithValue("@showId", showId);
                dbCmd.Parameters.AddWithValue("@price", price);
                dbCmd.Parameters.AddWithValue("@seatId", seat.SeatId);
                dbCmd.Parameters.AddWithValue("@bookingId", bookingId);
                rc = dbCmd.ExecuteNonQuery();
            }
            catch (SqlException sqlE)
            {
                Console.WriteLine("InsertTicket(...) - SQL Error");
                Console.WriteLine(sqlE.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine("InsertTicket(...) - Exception Error");
                Console.WriteLine(e.Message);
            }

            con.CloseConnection();

            return rc;
        }


        public int GetTicketId(Ticket ticketObj)
        {
            int ticketId = -1;
            string select = "SELECT ticketId " +
                            "FROM Ticket_xp " +
                            "WHERE showId = @showId AND seatId = @seatId";
            Console.WriteLine(select);

            try
            {
                IDbShow dbShow = new DbShow();
                int showId = dbShow.GetShowId(ticketObj.Show);

                dbCmd = con.GetDbCommand(select);
                dbCmd.Parameters.AddWithValue("@showId", showId);
                dbCmd.Parameters.AddWithValue("@seatId", ticketObj.Seat.SeatId);

                ticketId = (int)dbCmd.ExecuteScalar();
            }

            catch (SqlException sqlE)
            {
                Console.WriteLine("sql Error in DbTicket - GetTicketId");
                Console.WriteLine(sqlE.Message);
            }

            catch (Exception e)
            {
                Console.WriteLine("Error in DbTicket - GetTicketId");
                Console.WriteLine(e.Message);
            }
            con.CloseConnection();

            return ticketId;
        }


        public int GetNextTicketId()
        {
            String query = "SELECT MAX(ticketId) FROM Ticket_xp";
            int nextInt = 0;
            try
            {
                dbCmd = con.GetDbCommand(query);
                nextInt = (Int32)dbCmd.ExecuteScalar();
                nextInt++;
                con.CloseConnection();
                return nextInt;
            }

            catch (InvalidCastException e)
            {
                //Console.WriteLine("Error in getNextTicketId");
                //Console.WriteLine(e.Message);
                con.CloseConnection();
                return 0;
            }
            catch (Exception e)
            {
                Console.WriteLine("Error in getNextTicketId");
                Console.WriteLine(e.Message);
                con.CloseConnection();
                return -1;
            }
        }

        public List<Ticket> FindTickets(int bookingId, bool retriveAssociation)
        {
            List<Ticket> tickets = new List<Ticket>();
            SqlDataReader dbReader = null;
            Ticket ticketObj = null;

            string select = "SELECT bookingId, Show_xp.cName, Show_xp.date, price, Ticket_xp.seatId "
                          + "FROM Show_xp, Ticket_xp "
                          + "WHERE bookingId = @bookingId AND Show_xp.showId = Ticket_xp.showId";

            Console.WriteLine(select);
            
            try
            {
                dbCmd = con.GetDbCommand(select);
                dbCmd.Parameters.AddWithValue("@bookingId", bookingId);
                dbReader = dbCmd.ExecuteReader();

                while (dbReader.Read())
                {
                    ticketObj = BuildTicket(dbReader);
                    tickets.Add(ticketObj);
                }

                 dbReader.Close(); // lukkes da der kun kan være en åben af gangen her. i if-sætningen bliver der brugt en SqlDataReader igen inde på dbCinemaHall.

                if (retriveAssociation)
                {
                    foreach(Ticket ticketObj2 in tickets)
                    {
                        //Assocering til show. Ikke nødvendig?
                        //IDbShow dbShow = new DbShow();
                        //DateTime dateTime = ticketObj2.Show.DateTimeStart;
                        //string cName = ticketObj2.Show.CinemaHall.Name;
                        //Show showObj = dbShow.FindShow(dateTime, cName, true);
                        //ticketObj2.Show = showObj;

                        //Assocering til seat.
                        IDbSeat dbSeat = new DbSeat();
                        Seat seatObj = new Seat();
                        seatObj = dbSeat.FindSeat(ticketObj2.Seat.SeatId, false);
                        ticketObj2.Seat = seatObj;

                        // Associering til booking ikke nødvendig.

                    }
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine("sql Error in DbTicket - FindTickets");
                Console.WriteLine(e.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error in DbTicket - FindTickets");
                Console.WriteLine(e.Message);
            }

            con.CloseConnection();
            return tickets;
        }

        public int FindBookingIdFromTicket(Ticket ticketObj)
        {
            int bookingId = -1;

            string select = "SELECT Ticket_xp.bookingId "
                          + "FROM Ticket_xp, Show_xp "
                          + "WHERE seatId = @seatId AND Ticket_xp.showId = Show_xp.showId";
            Console.WriteLine(select);

            try
            {
                dbCmd = con.GetDbCommand(select);
                dbCmd.Parameters.AddWithValue("@seatId", ticketObj.Seat.SeatId);
                bookingId = (Int32)dbCmd.ExecuteScalar();
            }

            catch (SqlException e)
            {
                Console.WriteLine("sql Error in DbTicket");
                Console.WriteLine(e.Message);
                con.CloseConnection();
            }
            catch (Exception e)
            {
                Console.WriteLine("Error in DbTicket");
                Console.WriteLine(e.Message);
                con.CloseConnection();
            }
            con.CloseConnection();

            return bookingId;
        }

               


        private Ticket BuildTicket(SqlDataReader dbReader)
        {
            Ticket ticketObj = new Ticket();
            Show showObj = new Show();
            CinemaHall cinemaObj = new CinemaHall();
            Seat seatObj = new Seat();
            try
            {
                cinemaObj.Name = dbReader["cName"].ToString();
                showObj.CinemaHall = cinemaObj;
                showObj.DateTimeStart = (DateTime)dbReader["date"];
                ticketObj.Show = showObj;

                ticketObj.Price = Convert.ToDouble(dbReader["price"]);

                seatObj.SeatId = dbReader["seatId"].ToString();
                ticketObj.Seat = seatObj;
            }
            catch (Exception e)
            {
                Console.WriteLine("Error in building ticketObj");
                Console.WriteLine(e.Message);
            }

            return ticketObj;
        }
    }
}
